import sqlite3
from collections import Counter

import matplotlib.pyplot as plt
import pandas as pd

font_path = "SimHei"#黑体，否则无法显示中文

# 设置图表默认字体
plt.rcParams['font.sans-serif'] = font_path
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
# 连接数据库
conn = sqlite3.connect('city_weather.db') # your path to data base
# 查询数据
df = pd.read_sql_query("SELECT name, date, weather_desc FROM weathers", conn)

# 为每个城市生成天气统计图表
for city in df['name'].unique():
    city_data = df[df['name'] == city]

    # 将天气描述进行计数
    weather_counts = Counter(city_data['weather_desc'])

    # 准备绘制柱状图
    plt.figure(figsize=(10, 6))  # 设置图形大小
    plt.bar(weather_counts.keys(), weather_counts.values(), color=['skyblue', 'grey'])
    plt.title(f'7-Day Weather Forecast for {city}')
    plt.xlabel('Weather Condition')
    plt.ylabel('Frequency')
    plt.xticks(rotation=45)  # 旋转x轴标签，以便于阅读
    plt.legend([f'{key} days' for key in weather_counts.keys()])

    # 保存图形
    plt.savefig(f'bar/{city}_weather_forecast.png')
    plt.close()  # 关闭图形，以便于生成下一个图形

# 关闭数据库连接
conn.close()